﻿'========================================================================
'功能:資產資料庫存取物件
'作者:andy
'日期: 2011/3/25
'========================================================================

Imports Microsoft.VisualBasic
Imports System
Imports System.Collections.Generic
Imports System.Data.SqlClient
Imports System.Text
Imports System.Data
Imports MySql.Data.MySqlClient
Imports System.Net.Mail
Namespace AssetManage.DAO

    Public Class AssetLogDAO
        Inherits OAMS.DAO.BaseDAO

        Dim _dbName As String
        Public Sub New(ByVal dbName As String)
            _dbName = dbName
        End Sub
        Public Function GetAssetLogData(ByVal assetdef_id As String, ByVal asset_owner As String, ByVal asset_SerialNo As String, ByVal Dept As String, ByVal HouseID As String, ByVal MAC As String, ByVal ComputerName As String, ByVal IP As String) As DataTable
            Dim sb As New StringBuilder()
            Dim sqlParams As SqlParameter() = New SqlParameter(6) {}
            If assetdef_id = "" Then
                assetdef_id = "0"
            End If
            sb.AppendLine(" select '0' as ModiType,1 as SeqNo, F.assetdef_nameEN as DefName,'' as OwnerName,'' as UseName , A.*  from V_AST_ASSETALL A left join  C_AST_ASSETDEF F on A.assetdef_id=F.def_id ")
            sb.AppendLine("where 1=1  and asset_status='Distributed'")
            sb.AppendLine(" and (   assetdef_id=@assetdef_id OR @assetdef_id=0) ")
            sb.AppendLine(" and  (asset_owner like '%'+@asset_owner+'%')")
            sb.AppendLine(" and  (asset_serialNo like '%'+@asset_serialNo+'%' )")
            sb.AppendLine(" and  (Dept like '%'+@Dept+'%')")
            sb.AppendLine(" and  (MAC1 like '%'+@MAC+'%' or MAC2 like '%'+@MAC+'%' )")
            sb.AppendLine(" and  (ComputerName like '%'+@ComputerName+'%')")
            sb.AppendLine(" and  (HouseID =@HouseID or @HouseID=0)")
            '  sb.AppendLine(" and  (IP=@IP or IP=@IP or @IP='')")

            sqlParams(0) = New SqlParameter("@assetdef_id", SqlDbType.Int)
            sqlParams(0).Value = assetdef_id
            sqlParams(1) = New SqlParameter("@asset_owner", SqlDbType.NVarChar, asset_owner.Length)
            sqlParams(1).Value = asset_owner
            sqlParams(2) = New SqlParameter("@asset_serialNo", SqlDbType.NVarChar, asset_SerialNo.Length)
            sqlParams(2).Value = asset_SerialNo
            sqlParams(3) = New SqlParameter("@Dept", SqlDbType.NVarChar, Dept.Length)
            sqlParams(3).Value = Dept
            sqlParams(4) = New SqlParameter("@MAC", SqlDbType.NVarChar, MAC.Length)
            sqlParams(4).Value = MAC
            sqlParams(5) = New SqlParameter("@ComputerName", SqlDbType.NVarChar, ComputerName.Length)
            sqlParams(5).Value = ComputerName
            sqlParams(6) = New SqlParameter("@HouseID", SqlDbType.NVarChar, IP.Length)
            sqlParams(6).Value = HouseID
            'Dim dt As DataTable = SqlHelper.ExecuteDataset(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams).Tables(0)


            Dim cmd As MySqlCommand
            Dim con As MySqlConnection = myMYSqlConn(_dbName)
            Dim uDAO As OAMS.DAO.UsersDAO = New OAMS.DAO.UsersDAO(_dbName)
            Dim aDao As AssetDAO = New AssetDAO(_dbName)
            Try
                con.Open()

                Dim sb1 As New StringBuilder()
                Dim tablename As String = Now.ToString("HHmmssfffff")
                'sb1.AppendLine("Create TEMPORARY  table Temp" + tablename + "(asset_serialNo varchar(50) )")
                'cmd = New MySqlCommand(sb1.ToString(), con)
                'cmd.ExecuteNonQuery()
                'If assetdef_id <> "0" Or asset_SerialNo <> "" Or Dept <> "" Or HouseID <> "0" Or MAC <> "" Or ComputerName <> "" Or IP <> "" Or asset_owner <> "" Then
                '    For ii As Integer = 0 To dt.Rows.Count - 1
                '        sb1.Length = 0
                '        sb1.AppendLine("Insert into Temp" + tablename + "   values(?asset_serialNo)")
                '        cmd = New MySqlCommand(sb1.ToString(), con)
                '        cmd.Parameters.AddWithValue("?asset_serialNo", dt.Rows(ii)("asset_serialNo").ToString())
                '        cmd.ExecuteNonQuery()

                '    Next

                'End If








                sb1.Length = 0
                If assetdef_id <> "0" Or asset_SerialNo <> "" Or Dept <> "" Or HouseID <> "0" Or MAC <> "" Or ComputerName <> "" Or IP <> "" Or asset_owner <> "" Then
                    sb1.AppendLine("select A.*, C.assetdef_nameEN as DefName ,ifNull(B.emp_name,A.asset_Owner) as  ownerName ,user1 as  UseName from v_ast_assetalllog A left join `auoref`.`emp_data_all` B on A.asset_owner=B.emp_no   left join  C_AST_ASSETDEF  C on A.assetdef_id=C.def_id  where  1=1 ")
                    If assetdef_id <> "0" Then
                        sb1.AppendLine("  and assetdef_id =" + assetdef_id)
                    End If
                    If asset_SerialNo <> "" Then
                        sb1.AppendLine("  and asset_SerialNo ='" + asset_SerialNo + "'")
                    End If
                    If Dept <> "" Then
                        sb1.AppendLine("  and( Dept='" + Dept + "' or FromDept='" + Dept + "')")
                    End If

                    If HouseID <> "0" Then
                        sb1.AppendLine("  and HouseID='" + HouseID + "'")
                    End If
                    If MAC <> "" Then
                        sb1.AppendLine("  and (MAC1 like '" + MAC + "%' or MAC2 like '" + MAC + "%')")
                    End If
                    If ComputerName <> "" Then
                        sb1.AppendLine("  and ComputerName like '" + ComputerName + "%'")
                    End If

                    If asset_owner <> "" Then
                        sb1.AppendLine("  and( asset_owner = '" + asset_owner + "' or FromAsset_Owner='" + asset_owner + "')")
                    End If


                    '                sb1.AppendLine(")")
                Else

                    sb1.AppendLine("select A.*, C.assetdef_nameEN as DefName ,B.emp_name as  ownerName ,user1 as UseName from v_ast_assetalllog A left join `auoref`.`emp_data_all` B on A.asset_owner=B.emp_no   collate utf8_unicode_ci  left join  C_AST_ASSETDEF  C on A.assetdef_id=C.def_id  ")

                End If

                sb1.AppendLine("order by A.asset_serialNo ,A.ModiDate")

                cmd = New MySqlCommand(sb1.ToString(), con)

                Dim oDbAdapter As MySqlDataAdapter
                oDbAdapter = New MySqlDataAdapter(cmd)
                Dim oTable As Data.DataTable
                oTable = New Data.DataTable()
                oDbAdapter.Fill(oTable)


                'sb1.Length = 0

                'sb1.AppendLine("drop table Temp" + tablename)
                'cmd = New MySqlCommand(sb1.ToString(), con)
                'cmd.ExecuteNonQuery()

                'For Each dr In oTable.Rows
                '    ' dr("DefName") = aDao.GetAssetTypeName(dr("assetdef_id").ToString)
                '    ' dr("ownerName") = uDAO.GetUserName(dr("asset_owner").ToString)
                'Next
                con.Close()
                Return oTable

            Catch ex As Exception
                con.Close()
                Throw New Exception(ex.Message)
            Finally



            End Try
           



        End Function

        Public Function GetFromDeptFromAssetOwner(ByVal asset_serialno As String) As DataTable
            Dim con As MySqlConnection
            Try
                Dim sb1 As StringBuilder = New StringBuilder()
                sb1.AppendLine(" select max(SeqNo) as MAX1 from v_ast_assetalllog where asset_serialNo=?asset_serialNo and (modiType='4' or moditype='7' )and fromDept<>'' ")

                Dim oTable As Data.DataTable
                Dim cmd As MySqlCommand

                con = myMYSqlConn(_dbName)
                cmd = New MySqlCommand(sb1.ToString(), con)
                cmd.Parameters.AddWithValue("?asset_serialNo", asset_serialno)
                Dim oDbAdapter As MySqlDataAdapter
                oDbAdapter = New MySqlDataAdapter(cmd)
                oTable = New Data.DataTable()
                oDbAdapter.Fill(oTable)
                Dim max As Integer
                If oTable.Rows(0)("MAX1").ToString <> "" Then
                    max = Integer.Parse(oTable.Rows(0)("MAX1").ToString)
                Else
                    max = 0
                End If
                con.Close()
                If max = 0 Then
                    Return Nothing
                Else
                    Return GetAssetLogDetail(asset_serialno, max.ToString)
                End If
            Catch ex As Exception
                con.Close()
                Throw ex
            End Try

        End Function



        Public Function GetAssetLogDetail(ByVal asset_SerialNo As String, ByVal seqno As String) As DataTable
            Dim sb As New StringBuilder()
            Dim sqlParams As SqlParameter() = New SqlParameter(6) {}
           

            Dim cmd As MySqlCommand
            Dim con As MySqlConnection = myMYSqlConn(_dbName)
            Dim aDao As AssetDAO = New AssetDAO(_dbName)
            Try
                con.Open()

                Dim sb1 As New StringBuilder()
             


                sb1.Length = 0
                sb1.AppendLine("select A.*,B.emp_name as ownerName,C.emp_name as CrtName ,D.emp_name as ModiName  ")
                sb1.AppendLine("from v_ast_assetalllog A  left join `auoref`.`emp_data_all` B  on A.asset_owner=B.emp_no   collate utf8_unicode_ci ")
                sb1.AppendLine(" left join `auoref`.`emp_data_all` C  on A.CrtUID=C.emp_no  collate utf8_unicode_ci ")
                sb1.AppendLine(" left join `auoref`.`emp_data_all` D  on A.ModiUID=D.emp_no  collate utf8_unicode_ci")


                sb1.AppendLine(" where A.asset_serialNo =?asset_serialNo and A.SeqNo=?SeqNo ")

                cmd = New MySqlCommand(sb1.ToString(), con)
                cmd.Parameters.AddWithValue("?asset_serialNo", asset_SerialNo)
                cmd.Parameters.AddWithValue("?SeqNo", seqno)
                Dim oDbAdapter As MySqlDataAdapter
                oDbAdapter = New MySqlDataAdapter(cmd)
                Dim oTable As Data.DataTable
                oTable = New Data.DataTable()
                oDbAdapter.Fill(oTable)

                Return oTable

            Catch ex As Exception
            Finally
                con.Close()


            End Try




        End Function




    End Class

End Namespace
